'''
Created on Oct 28, 2010

@author: linh-cl
'''
from pysqlite2 import dbapi2 as sqlite
from core.Note import Note
from util.CategoryManager import CategoryManager
from util.DateTimeUtil import str2DateTime

class NoteManager( object ):
  SELECT_STR = 'SELECT nID,cID,nTitle,nContent,nCreateTime, nModifyTime,nStatus,nRate,nReadCount FROM tbNotes'
  UPDATE_STR = 'UPDATE tbNotes SET cID=?,nTitle=?,nContent=?,nCreateTime=?, nModifyTime=?,nStatus=?,nRate=?,nReadCount=? WHERE nID=?'

  def __init__ ( self, conn = None ):
    self.conn = conn

  def getAllNotes( self, order = 'nTitle' ):
    cur = self.conn.cursor()
    allNotes = []
    cur.execute( self.SELECT_STR + ' ORDER BY ' + order )
    for ( id, cID, title, content, createTime, modifyTime, status, rate, readCount ) in cur:
      cTime = str2DateTime( createTime )
      mTime = str2DateTime( modifyTime )
      catMan = CategoryManager( self.conn )
      cat = catMan.getCategory( cID )
      note = Note( id, cat, title, content, cTime, mTime, rate, readCount, status )
      allNotes.append( note )
    if len( allNotes ) > 0:
      return allNotes
    else:
      return None
  def getNoteById ( self, id ):
    cur = self.conn.cursor()
    params = ( id, )
    cur.execute( self.SELECT_STR + ' WHERE nID=?', params )
    arr = cur.fetchone()
    cTime = str2DateTime( arr[4] )
    mTime = str2DateTime( arr[5] )
    catMan = CategoryManager( self.conn )
    cat = catMan.getCategory( arr[1] )
    note = Note( id, cat, arr[2], arr[3], cTime, mTime, arr[7], arr[8], arr[6] )
    return note
  def updateNote( self, note ):
    cur = self.conn.cursor()
    params = ( note.get_category().get_id(), note.get_title(), note.get_content(), note.get_create_time(), note.get_modify_time(), note.get_status(), note.get_rate(), note.get_read_count() )
    cur.execute( self.UPDATE_STR , params )
  def deleteNote( self, note ):
    cur = self.conn.cursor()
    params = ( note.get_category().get_id(), )
    cur.execute( 'DELETE FROM tbNotes WHERE nID=?', params )
  def getNotes( self, category = None, title = None, content = None, lcreate = None, hcreate = None, lmodify = None, hmodify = None, status = None, rate = None, order = 'nTitle' , isGlob = False ):
    cur = self.conn.cursor()
    allNotes = []
    params = ()
    first = True
    sqlstr = self.SELECT_STR

    if category != None:
      if first:
        sqlstr += ' WHERE cID=?'
        first = False
      else:
        sqlstr += 'and cID=?'
      params += category.get_id(),
    if title != None:
      if first:
        sqlstr += ' WHERE '
        if isGlob:
          sqlstr += 'nTitle GLOB ? '
        else:
          sqlstr += 'nTitle LIKE ? '
        first = False
      else:
        if isGlob:
          sqlstr += ' and nTitle GLOB ?'
        else:
          sqlstr += ' and nTitle LIKE ?'
      if isGlob:
        params += title,
      else:
        params += '%' + title + '%',
    if content != None:
      if first:
        if isGlob:
          sqlstr += ' WHERE nContent GLOB ? '
        else:
          sqlstr += ' WHERE nContent LIKE ? '
        first = False
      else:
        if isGlob:
          sqlstr += ' and nContent GLOB ? '
        else:
          sqlstr += ' and nContent like ? '
      if isGlob:
        params += content,
      else:
        params += '%' + content + '%',
    if lcreate != None and hcreate != None:
      if first:
        sqlstr += ' WHERE nCreateTime BETWEEN ? and ? '
        first = False
      else:
        sqlstr += ' and nCreateTime BETWEEN ? and ?'
      params += lcreate, hcreate,
    elif lcreate != None:
      #find > create time
      if first:
        sqlstr += ' WHERE nCreateTime > ? '
        first = False
      else:
        sqlstr += ' and nCreateTime > ? '
      params += lcreate,
    elif hcreate != None:
      #find < create time
      if first:
        sqlstr += ' WHERE nCreateTime < ? '
        first = False
      else:
        sqlstr += ' and nCreateTime < ? '
      params += hcreate,
    if status != None:
      if first:
        sqlstr += ' WHERE nStatus = ? '
        first = False
      else:
        sqlstr += ' and nStatus = ? '
      params += status,
    if rate != None:
      if first:
        sqlstr += ' WHERE nRate = ? '
        first = False
      else:
        sqlstr += ' and nRate = ? '
      params += rate,
    cur.execute( sqlstr, params )
    for ( id, cID, title, content, createTime, modifyTime, status, rate, readCount ) in cur:
      catMan = CategoryManager( self.conn )
      cat = catMan.getCategory( cID )
      cTime = str2DateTime( createTime )
      mTime = str2DateTime( modifyTime )
      note = Note( id, cat, title, content, cTime, mTime, rate, readCount, status )
      allNotes.append( note )
    if len( allNotes ) > 0:
      return allNotes
    else:
      return None
